package com.xmc.dao;

import com.xmc.pojo.User;
import com.xmc.utils.DBManager;

import java.sql.ResultSet;
import java.sql.SQLException;
 
/**
 * @description 数据库连接与操作类用于增删改查数据并返回给servlet使用
 * @author WANGZIC
 *
 */
public class UserDao {

    DBManager dbManager = new DBManager();

    public User login(String code, String password, String role){
        User user = new User();
        StringBuilder sql = new StringBuilder("select * from user where 1=1");
        if (code != null){
            sql.append(" and code = '"+code+"'");
        }
        if (password != null){
            sql.append(" and password = '" + password +"'");
        }
        if (role != null){
            sql.append(" and role = '"+role+"'");
        }
        System.out.println("select : "+ sql);
        ResultSet set = null;
        try {
            set = dbManager.executeQuery(sql.toString());
                while (set.next()) {
                    user.setId(set.getInt("id"));
                    user.setBirthday(set.getDate("birthday"));
                    user.setGender(set.getInt("gender"));
                    user.setCode(set.getString("code"));
                    user.setDepartment(set.getString("department"));
                    user.setEmail(set.getString("email"));
                    user.setImgurl(set.getString("imgurl"));
                    user.setPhone(set.getLong("phone"));
                    user.setUsername(set.getString("username"));
                    user.setMajor(set.getString("major"));
                    user.setRole(set.getInt("role"));
                    user.setStudentclass(set.getString("studentclass"));
                    user.setPassword(set.getString("password"));
                    return user;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
       return null;
    }


    public boolean updateImg(String code, String imgurl){
        StringBuilder sql = new StringBuilder("update user set imgurl = '" + imgurl + "' "+ "where code ="+" '"+code+"'");
        System.out.println("select : "+ sql);
        boolean res = false;
        try {
            res = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

//    public boolean updateImg(Long code, String imgurl){
//        StringBuilder sql = new StringBuilder("update user set imgurl = '" + imgurl + "' "+ "where code ="+" "+code);
//        System.out.println("select : "+ sql);
//        boolean res = false;
//        try {
//            res = dbManager.executeUpdate(sql.toString());
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return res;
//    }

    public boolean checkPwd(Integer id , String password){
        StringBuilder sql = new StringBuilder("select count(*) from user where password = '" + password + "' "+ "and id ="+" "+id);
        System.out.println("select : "+ sql);
        boolean res = false;
        ResultSet r = null;
        try {
            r = dbManager.executeQuery(sql.toString());
            r.next();
            if(r.getInt(1)>0){
                res = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    public boolean updateStudent(int stuId, Long phone, String email, String password) {
        StringBuilder sql = new StringBuilder("update user set");
        if (email != null && email.length() > 0){
            sql.append(" email = '"+email+"'");
        }
        if (phone != null && phone.toString().length() > 0){
            sql.append(",phone = "+phone);
        }
        if (password != null && password.length() > 0){
            sql.append(" password = '"+password+"'");
        }
        sql.append(" where id = " + stuId);
        System.out.println("select : "+ sql);
        boolean res = false;
        try {
            res = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    public String getUserIdByUsername(String username) {
        StringBuilder sql = new StringBuilder("select code from user where 1=1");
        if (username != null){
            sql.append(" and username = '"+username+"'");
        }
        System.out.println("select : "+ sql);
        ResultSet set = null;
        try {
            set = dbManager.executeQuery(sql.toString());
            while (set.next()) {
                return set.getString("code");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean updateScoreByTeacherAndStudentId(String studentid, float usualgrades, float examgrade, int totalgrade,String coursename) {

        StringBuilder sql = new StringBuilder("update course set ");
        if (usualgrades > 0){
            sql.append(" usualgrades = "+usualgrades);
        }
        if (examgrade > 0){
            sql.append(",examgrade = "+examgrade);
        }
        if (totalgrade > 0){
            sql.append(",totalgrade = "+totalgrade);
        }
        sql.append(" where coursename = '"+coursename+"' and studentid = '"+studentid+"'");
        System.out.println("select : "+ sql);
        boolean res = false;
        try {
            res = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
}